/*
 * KP ebdesk
 */

package com.ebdesk.controller.masterdata;

import com.ebdesk.model.masterdata.User;
import com.ebdesk.model.masterdata.database.DatabaseData;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Zulfikar <zulfikarfull@gmail.com>
 */
public class UserManagement {
    private DatabaseData databaseData;
    private String tableUser = "pbb.user_management";
    private int totalRow;

    public UserManagement() throws ClassNotFoundException, IOException, SQLException, NullPointerException {
        databaseData = new DatabaseData();
    }

    public List<User> getAllUser(int pageNumber, int rowsPerPage) throws SQLException {
        ResultSet rs = getDatabaseData().queryData("select * from " + getTableUser() + 
                " limit " + rowsPerPage + " offset " + ((pageNumber - 1) * rowsPerPage));
        
        List<User> dataUser = new ArrayList<User>();
        ResultSet rst = getDatabaseData().queryData("select count(*) from " + getTableUser());
        while (rst.next()) {
            totalRow = rst.getInt(1);
        }
        while(rs.next()){
            User user = new User();
            
            user.setUsername(rs.getString(1));
            user.setName(rs.getString(2));
            user.setEmail(rs.getString(3));
            user.setPassword(rs.getString(4));
            user.setCreated_by(rs.getString(5));
            user.setCreated_date(rs.getTimestamp(6));
            user.setUser_type(Integer.parseInt(rs.getString(7)));

            dataUser.add(user);
        }

        return dataUser;

    }

    public List<User> SearchingUserByUsername(String username, int pageNumber, int rowsPerPage) throws SQLException {
        ResultSet rs = getDatabaseData().queryData("select * from " + getTableUser() + " where username like '%" + username.trim() + "%'" +
                " limit " + + rowsPerPage + " offset " + ((pageNumber - 1) * rowsPerPage));

        List<User> dataUser = new ArrayList<User>();
        ResultSet rst = getDatabaseData().queryData("select count(*) from " + getTableUser() + " where username like '%" + username.trim() + "%'");
        while (rst.next()) {
            totalRow = rst.getInt(1);
        }
        while(rs.next()){
            User user = new User();

            user.setUsername(rs.getString(1));
            user.setName(rs.getString(2));
            user.setEmail(rs.getString(3));
            user.setPassword(rs.getString(4));
            user.setCreated_by(rs.getString(5));
            user.setCreated_date(rs.getTimestamp(6));
            user.setUser_type(Integer.parseInt(rs.getString(7)));

            dataUser.add(user);
        }

        return dataUser;

    }

    public List<User> SearchingUserByName(String name, int pageNumber, int rowsPerPage) throws SQLException {
        ResultSet rs = getDatabaseData().queryData("select * from " + getTableUser() + " where name like '%" + name.trim() + "%'" + " limit " + rowsPerPage + " offset " + ((pageNumber - 1) * rowsPerPage));
        
        List<User> dataUser = new ArrayList<User>();
        ResultSet rst = getDatabaseData().queryData("select count(*) from " + getTableUser() + " where name like '%" + name.trim() + "%'");
        while (rst.next()) {
            totalRow = rst.getInt(1);
        }
        while(rs.next()){
            User user = new User();

            user.setUsername(rs.getString(1));
            user.setName(rs.getString(2));
            user.setEmail(rs.getString(3));
            user.setPassword(rs.getString(4));
            user.setCreated_by(rs.getString(5));
            user.setCreated_date(rs.getTimestamp(6));
            user.setUser_type(Integer.parseInt(rs.getString(7)));

            dataUser.add(user);
        }

        return dataUser;

    }

    public List<User> SearchingUserByEmail(String email, int pageNumber, int rowsPerPage) throws SQLException {
        ResultSet rs = getDatabaseData().queryData("select * from " + getTableUser() + " where email like '%" + email.trim() + "%'" + " limit " + rowsPerPage + " offset " + ((pageNumber - 1) * rowsPerPage));

        List<User> dataUser = new ArrayList<User>();
        ResultSet rst = getDatabaseData().queryData("select count(*) from " + getTableUser() + " where email like '%" + email.trim() + "%'");
        while (rst.next()) {
            totalRow = rst.getInt(1);
        }
        while(rs.next()){
            User user = new User();

            user.setUsername(rs.getString(1));
            user.setName(rs.getString(2));
            user.setEmail(rs.getString(3));
            user.setPassword(rs.getString(4));
            user.setCreated_by(rs.getString(5));
            user.setCreated_date(rs.getTimestamp(6));
            user.setUser_type(Integer.parseInt(rs.getString(7)));

            dataUser.add(user);
        }

        return dataUser;

    }

    public User getUser(String username) throws SQLException {
        ResultSet rs = getDatabaseData().queryData("select * from " + getTableUser() + " where username = '" + username + "'");

        User user = new User();
        while (rs.next()) {
            user.setUsername(rs.getString(1));
            user.setName(rs.getString(2));
            user.setEmail(rs.getString(3));
            user.setPassword(rs.getString(4));
            user.setCreated_by(rs.getString(5));
            user.setCreated_date(rs.getTimestamp(6));
            user.setUser_type(Integer.parseInt(rs.getString(7)));
        }
        return user;
    }

    public int addUser(User user) throws SQLException {
        try {
            String sql = "insert into " + getTableUser() + "(username, name, email, password, created_by, created_date, user_type) "+
                "values(?,?,?,md5(?),?,now(),?)";

            PreparedStatement ps = getDatabaseData().getPreparedStatement(sql);

            ps.setString(1, user.getUsername());
            ps.setString(2, user.getName());
            ps.setString(3, user.getEmail());
            ps.setString(4, user.getPassword());
            ps.setString(5, user.getCreated_by());
            //ps.setTimestamp(6, user.getCreated_date());
            ps.setString(6, user.getUser_type()+"");

            return getDatabaseData().updateData(ps);

        } catch (org.postgresql.util.PSQLException ex) {
            return -1;
        }

    }

    public int removeUser(User user) throws SQLException {
        String sql = "delete from " + getTableUser() + " where username = '" + user.getUsername() + "'";

        PreparedStatement ps = getDatabaseData().getPreparedStatement(sql);

        return getDatabaseData().updateData(sql);
    }

    public int removeUser(String username) throws SQLException {
        String sql = "delete from " + getTableUser() + " where username = '" + username + "'";

        PreparedStatement ps = getDatabaseData().getPreparedStatement(sql);

        return getDatabaseData().updateData(sql);
    }

    public int editUser(User user) throws SQLException {
            String sql = "update " + getTableUser() + " set name = ?, email = ?, password = md5(?), created_by = ?, created_date = ?, user_type= ?"+
                " where username = ?";

            PreparedStatement ps = getDatabaseData().getPreparedStatement(sql);

            ps.setString(7, user.getUsername());
            ps.setString(1, user.getName());
            ps.setString(2, user.getEmail());
            ps.setString(3, user.getPassword());
            ps.setString(4, user.getCreated_by());
            ps.setTimestamp(5, user.getCreated_date());
            ps.setString(6, user.getUser_type()+"");

            return getDatabaseData().updateData(ps);

    }

    /**
     * @return the databaseData
     */
    public DatabaseData getDatabaseData() {
        return databaseData;
    }

    /**
     * @param databaseData the databaseData to set
     */
    public void setDatabaseData(DatabaseData databaseData) {
        this.databaseData = databaseData;
    }

    /**
     * @return the tableUser
     */
    public String getTableUser() {
        return tableUser;
    }

    /**
     * @param tableUser the tableUser to set
     */
    public void setTableUser(String tableuser) {
        this.tableUser = tableuser;
    }

    /**
     * @return the totalRow
     */
    public int getTotalRow() {
        return totalRow;
    }

}
